<?php
class AChargingWapmaster extends CActiveRecord
{
    public function GetUser($search, $currentPage, $numberRecordPerPage)
    {
        $search["fromDate"] .= " 23:59:59";
        $startRecord = ($currentPage - 1) * $numberRecordPerPage;
        
        $condition = "";        
        $condition .= " AND create_time>=" . strtotime($search["toDate"]);
        $condition .= " AND create_time<=" . strtotime($search["fromDate"]);
        $condition .= !empty($search["telco"]) ? " AND telco=" . mysql_escape_string($search["telco"]) : "";                
        $condition .= !empty($search["price"]) ? " AND price=" . mysql_escape_string($search["price"]) : "";        
        $condition .= !empty($search["content_type"]) ? " AND content_type=" . mysql_escape_string($search["content_type"]) : "";        
        $condition .= " AND status=1";        
        
        if(!empty($search["keyword"])){
            $sql = "SELECT id FROM c_user WHERE username='" . mysql_escape_string($search["keyword"]) . "'";
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryRow();    
            
            $condition .= empty($rows) ? " AND userId=0" : " AND userId=" . $rows["id"];
        }
          
        /* Tiêu chí sắp xếp mặc định */      
        $sql = "SELECT userId, sum(price) as total_price FROM c_wapcharging_log WHERE 1 " . $condition . " GROUP BY userId ORDER BY total_price DESC LIMIT " . $startRecord . ", " . $numberRecordPerPage;        
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryAll();    
        return $rows;
    }  
    
    public function GetTotalUser($search)
    {
        $search["fromDate"] .= " 23:59:59";                
        $condition = "";        
        $condition .= " AND create_time>=" . strtotime($search["toDate"]);
        $condition .= " AND create_time<=" . strtotime($search["fromDate"]);
        $condition .= !empty($search["telco"]) ? " AND telco=" . mysql_escape_string($search["telco"]) : "";                
        $condition .= !empty($search["price"]) ? " AND price=" . mysql_escape_string($search["price"]) : "";        
        $condition .= !empty($search["content_type"]) ? " AND content_type=" . mysql_escape_string($search["content_type"]) : "";        
        $condition .= " AND status=1";        
        
        if(!empty($search["keyword"])){
            $sql = "SELECT id FROM c_user WHERE username='" . mysql_escape_string($search["keyword"]) . "'";
            $command = Yii::app()->db->createCommand($sql);
            $rows = $command->queryRow();    
            
            $condition .= empty($rows) ? " AND userId=0" : " AND userId=" . $rows["id"];
        }
          
        /* Tiêu chí sắp xếp mặc định */      
        $sql = "SELECT count(DISTINCT userId) FROM c_wapcharging_log WHERE 1 " . $condition;
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryRow();    
        return $rows["count(DISTINCT userId)"];   
    }
    
    public function getReport($search)
    {
        $search["fromDate"] .= " 23:59:59";
        
        $condition = "";
        $condition .= $search["list_user_id"]!="" ? " AND userId IN (" . mysql_escape_string($search["list_user_id"]) . ")" : "";
        $condition .= " AND create_time>=" . strtotime($search["toDate"]);
        $condition .= " AND create_time<=" . strtotime($search["fromDate"]);
        $condition .= !empty($search["telco"]) ? " AND telco=" . mysql_escape_string($search["telco"]) : "";                
        $condition .= !empty($search["price"]) ? " AND price=" . mysql_escape_string($search["price"]) : "";            
        $condition .= !empty($search["content_type"]) ? " AND content_type=" . mysql_escape_string($search["content_type"]) : "";        
        $condition .= " AND status=1";        
        
        $sql = "SELECT count(id), userId, price, sum(price), sum(price_telco), sum(price_charging), sum(price_mtop), sum(price_wapmaster), sum(price_producer) FROM c_wapcharging_log WHERE 1 " . $condition . " GROUP BY userId, price";        
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryAll();
        
        $sms = array();
        foreach($rows as $row){
            $userId = $row["userId"];
            if(!isset($sms[$userId]["total"])) $sms[$userId]["total"] = 0;
            if(!isset($sms[$userId]["total_500"])) $sms[$userId]["total_500"] = 0;
            if(!isset($sms[$userId]["total_1000"])) $sms[$userId]["total_1000"] = 0;
            if(!isset($sms[$userId]["total_2000"])) $sms[$userId]["total_2000"] = 0;
            if(!isset($sms[$userId]["total_3000"])) $sms[$userId]["total_3000"] = 0;
            if(!isset($sms[$userId]["total_4000"])) $sms[$userId]["total_4000"] = 0;
            if(!isset($sms[$userId]["total_5000"])) $sms[$userId]["total_5000"] = 0;
            if(!isset($sms[$userId]["total_10000"])) $sms[$userId]["total_10000"] = 0;
            if(!isset($sms[$userId]["total_15000"])) $sms[$userId]["total_15000"] = 0;
            
            if(!isset($sms[$userId]["telco"])) $sms[$userId]["telco"] = 0;
            if(!isset($sms[$userId]["charging"])) $sms[$userId]["charging"] = 0;
            if(!isset($sms[$userId]["mtop"])) $sms[$userId]["mtop"] = 0;
            if(!isset($sms[$userId]["wapmaster"])) $sms[$userId]["wapmaster"] = 0;
            if(!isset($sms[$userId]["producer"])) $sms[$userId]["producer"] = 0;
            
            $sms[$userId]["total"] += $row["count(id)"];            
            $sms[$userId]["total_500"] += $row["price"]==500 ? $row["count(id)"] : 0;
            $sms[$userId]["total_1000"] += $row["price"]==1000 ? $row["count(id)"] : 0;
            $sms[$userId]["total_2000"] += $row["price"]==2000 ? $row["count(id)"] : 0;
            $sms[$userId]["total_3000"] += $row["price"]==3000 ? $row["count(id)"] : 0;
            $sms[$userId]["total_4000"] += $row["price"]==4000 ? $row["count(id)"] : 0;
            $sms[$userId]["total_5000"] += $row["price"]==5000 ? $row["count(id)"] : 0;
            $sms[$userId]["total_10000"] += $row["price"]==10000 ? $row["count(id)"] : 0;
            $sms[$userId]["total_15000"] += $row["price"]==15000 ? $row["count(id)"] : 0;
            
            $sms[$userId]["telco"] += $row["sum(price_telco)"];
            $sms[$userId]["charging"] += $row["sum(price_charging)"];
            $sms[$userId]["mtop"] += $row["sum(price_mtop)"];
            $sms[$userId]["wapmaster"] += $row["sum(price_wapmaster)"];
            $sms[$userId]["producer"] += $row["sum(price_producer)"];
        }
        
        if(!empty($search["list_user_id"])){
            $sql = "SELECT id, username FROM c_user WHERE id IN (" . $search["list_user_id"] . ")";
            $command = Yii::app()->db->createCommand($sql);
            $rowsUser = $command->queryAll();
            $replaceUser = array();
            foreach($rowsUser as $row){                
                $replaceUser[$row["id"]] = $row["username"];            
            }
        }
        
        foreach($sms as $userId=>$row){
            $sms[$userId]["username"] = isset($replaceUser[$userId]) ? $replaceUser[$userId] : "";            
        }
        
        return $sms;
    }
    
    public function getReportByUser($search, $currentPage, $numberRecordPerPage)
    {
        $startRecord = ($currentPage - 1) * $numberRecordPerPage;
        $search["fromDate"] .= " 23:59:59";
        
        $condition = "";
        $condition .= " AND userId=" . $search["userId"];
        $condition .= " AND create_time>=" . strtotime($search["toDate"]);
        $condition .= " AND create_time<=" . strtotime($search["fromDate"]);
        $condition .= !empty($search["telco"]) ? " AND telco=" . mysql_escape_string($search["telco"]) : "";                
        $condition .= !empty($search["price"]) ? " AND price=" . mysql_escape_string($search["price"]) : "";            
        $condition .= !empty($search["content_type"]) ? " AND content_type=" . mysql_escape_string($search["content_type"]) : "";        
        $condition .= " AND status=1";   

        $sql = "SELECT * FROM c_wapcharging_log WHERE 1 " . $condition . " ORDER BY create_time DESC LIMIT " . $startRecord . ", " . $numberRecordPerPage;        
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryAll();
        return $rows;
    }
    
    public function getTotalReportByUser($search)
    {        
        $search["fromDate"] .= " 23:59:59";
        
        $condition = "";
        $condition .= " AND userId=" . $search["userId"];
        $condition .= " AND create_time>=" . strtotime($search["toDate"]);
        $condition .= " AND create_time<=" . strtotime($search["fromDate"]);
        $condition .= !empty($search["telco"]) ? " AND telco=" . mysql_escape_string($search["telco"]) : "";                
        $condition .= !empty($search["price"]) ? " AND price=" . mysql_escape_string($search["price"]) : "";            
        $condition .= !empty($search["content_type"]) ? " AND content_type=" . mysql_escape_string($search["content_type"]) : "";        
        $condition .= " AND status=1";   

        $sql = "SELECT count(id) FROM c_wapcharging_log WHERE 1 " . $condition;
        $command = Yii::app()->db->createCommand($sql);
        $rows = $command->queryRow();
        return $rows["count(id)"];
    }
}  
?>